// 数据库-增删改查方法
const handleSqlCode = {
  add: function(table_name, data = null) { // 增加
    let str_item = '';
    Object.values(data).forEach(value => {
      str_item = str_item + "\'" + value + "\'" + ",";
    });
    let values_list = str_item.substring(0, str_item.length -1);

    let keys_list = Object.keys(data).toString(); 
    let sql_val = `INSERT INTO ${table_name} (${keys_list}) VALUES (${values_list})`;
    
    return sql_val;
  },
  query: function(table_name, data = null) { // 查询
    let sql_val = "";
    if(data.searchWord) { // 模糊查询
      sql_val = `SELECT * FROM ${table_name} WHERE (user_name LIKE '%${data.searchWord}%' OR user_address LIKE '%${data.searchWord}%') AND is_delete_status <> 0 LIMIT ${(data.pageIndex - 1) * (data.pageSize)}, ${data.pageSize}`
    } else if(data.type == 0) { // 除被逻辑删除，分页查询所有
      sql_val = `SELECT * FROM ${table_name} WHERE is_delete_status <> 0 LIMIT ${(data.pageIndex - 1) * (data.pageSize)}, ${data.pageSize}`;
    } else if(data.type == 1) { // 分页查询所有
      sql_val = `SELECT * FROM ${table_name} LIMIT ${(data.pageIndex - 1) * (data.pageSize)}, ${data.pageSize}`;
    } else { // 默认分页查询所有
      sql_val = `SELECT * FROM ${table_name} WHERE is_delete_status <> 0 LIMIT ${(data.pageIndex - 1) * (data.pageSize)}, ${data.pageSize}`;
    }
    return sql_val;
  },
  edit: function(table_name, data = null) { // 编辑
    let keys_list = Object.keys(data);
    let values_list = Object.values(data);

    let str_item = "";
    keys_list.forEach((key, i) => {
      values_list.forEach((value, j) => {
        if(i == j) str_item = `${str_item},${keys_list[i]}=${"\'" + values_list[i] + "\'"}`;
      });
    });
    str_item = str_item.substring(1, str_item.length);

    let sql_val = `UPDATE ${table_name} SET ${str_item}  WHERE id = ${data.id}`;

    return sql_val;
  },
  delete: function(table_name, data = null) { // 逻辑删除
    let sql_val = `UPDATE ${table_name} SET is_delete_status = 0 WHERE id = ${data.id}`;
    return sql_val;
  },
  multiDelete: function(table_name, data = null) { // 批量逻辑删除
    let sql_val = `UPDATE ${table_name} SET is_delete_status = 0 WHERE id in (${data.ids})`;
    return sql_val;
  },
  toTotal: function(table_name, data = null) { // 返回总条目数
    let sql_val = "";
    if(data.searchWord) {
      sql_val = `select count(*) AS total from ${table_name} WHERE (user_name LIKE '%${data.searchWord}%' OR user_address LIKE '%${data.searchWord}%') AND is_delete_status <> 0`;
    } else if(data.type == 0) {
      sql_val = `select count(*) AS total from ${table_name} WHERE is_delete_status <> 0`;
    } else if(data.type == 1) {
      sql_val = `select count(*) AS total from ${table_name}`;
    } else {
      sql_val = `select count(*) AS total from ${table_name} WHERE is_delete_status <> 0`;
    }
    return sql_val;
  }
};

module.exports = handleSqlCode;